# database_integration.py - 数据库集成模块
import sys
import os

# 添加项目根目录到Python路径
project_root = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
sys.path.append(project_root)

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from contextlib import contextmanager
from datetime import datetime
import logging

# 直接导入数据库配置和模型
from 数据库.config import config
from 数据库.database import Base, SessionLocal, engine
from 数据库.models.user import User
from 数据库.models.session import InterviewSession
from 数据库.models.dialogue import Dialogue
from 数据库.models.emotion import EmotionRecord
from 数据库.models.evaluation import Evaluation

# 设置日志
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

class DatabaseManager:
    def __init__(self):
        self.engine = engine
        self.SessionLocal = SessionLocal
        
    def create_tables(self):
        """创建所有数据库表"""
        try:
            Base.metadata.create_all(bind=self.engine)
            logger.info("数据库表创建成功")
        except Exception as e:
            logger.error(f"创建数据库表失败: {e}")
            raise

    @contextmanager
    def get_db_session(self):
        """获取数据库会话的上下文管理器"""
        session = self.SessionLocal()
        try:
            yield session
            session.commit()
        except Exception as e:
            session.rollback()
            logger.error(f"数据库操作失败: {e}")
            raise
        finally:
            session.close()

    # 用户相关操作
    def create_user(self, username, email, password_hash, role="candidate"):
        session = self.Session()
        try:
            user = User(
                username=username,
                email=email,
                password_hash=password_hash,
                role=role,
            )
            session.add(user)
            session.commit()
            session.refresh(user)  # 让 user.id 可用
            return user.id  # ✅ 只返回 ID
        except Exception as e:
            session.rollback()
            raise e
        finally:
            session.close()

    def get_user_by_email(self, email):
        """根据邮箱获取用户"""
        with self.get_db_session() as session:
            return session.query(User).filter(User.email == email).first()

    def get_user_by_id(self, user_id):
        """根据ID获取用户"""
        with self.get_db_session() as session:
            return session.query(User).filter(User.id == user_id).first()

    # 面试会话相关操作
    def create_interview_session(self, user_id, session_type='ai_interview'):
        """创建面试会话"""
        with self.get_db_session() as session:
            interview_session = InterviewSession(
                user_id=user_id,
                start_time=datetime.now(),
                session_type=session_type,
                created_at=datetime.now()
            )
            session.add(interview_session)
            session.flush()
            return interview_session.id

    def update_interview_session(self, session_id, end_time=None, report_path=None):
        """更新面试会话"""
        with self.get_db_session() as session:
            interview_session = session.query(InterviewSession).filter(
                InterviewSession.id == session_id
            ).first()
            
            if interview_session:
                if end_time:
                    interview_session.end_time = end_time
                if report_path:
                    interview_session.report_path = report_path
                return True
            return False

    def get_interview_session(self, session_id):
        """获取面试会话"""
        with self.get_db_session() as session:
            return session.query(InterviewSession).filter(
                InterviewSession.id == session_id
            ).first()

    # 对话相关操作
    def save_dialogue(self, session_id, round_num, speaker, content):
        """保存对话记录"""
        with self.get_db_session() as session:
            dialogue = Dialogue(
                session_id=session_id,
                round=round_num,
                speaker=speaker,
                content=content,
                timestamp=datetime.now()
            )
            session.add(dialogue)
            return dialogue.id

    def get_session_dialogues(self, session_id):
        """获取会话的所有对话"""
        with self.get_db_session() as session:
            return session.query(Dialogue).filter(
                Dialogue.session_id == session_id
            ).order_by(Dialogue.round, Dialogue.timestamp).all()

    # 情绪记录相关操作
    def save_emotion_record(self, session_id, round_num, emotion_label, confidence, image_path=None):
        """保存情绪记录"""
        with self.get_db_session() as session:
            emotion_record = EmotionRecord(
                session_id=session_id,
                round=round_num,
                emotion_label=emotion_label,
                confidence=confidence,
                image_path=image_path,
                timestamp=datetime.now()
            )
            session.add(emotion_record)
            return emotion_record.id

    def get_session_emotions(self, session_id):
        """获取会话的所有情绪记录"""
        with self.get_db_session() as session:
            return session.query(EmotionRecord).filter(
                EmotionRecord.session_id == session_id
            ).order_by(EmotionRecord.round, EmotionRecord.timestamp).all()

    # 评估相关操作
    def save_evaluation(self, session_id, language_score, emotion_score, logic_score, overall_score, feedback):
        """保存面试评估"""
        with self.get_db_session() as session:
            evaluation = Evaluation(
                session_id=session_id,
                language_score=language_score,
                emotion_score=emotion_score,
                logic_score=logic_score,
                overall_score=overall_score,
                feedback=feedback,
                created_at=datetime.now()
            )
            session.add(evaluation)
            return evaluation.id

    def get_session_evaluation(self, session_id):
        """获取会话评估"""
        with self.get_db_session() as session:
            return session.query(Evaluation).filter(
                Evaluation.session_id == session_id
            ).first()

    # 统计相关操作
    def get_user_interview_count(self, user_id):
        """获取用户面试次数"""
        with self.get_db_session() as session:
            return session.query(InterviewSession).filter(
                InterviewSession.user_id == user_id
            ).count()

    def get_user_average_score(self, user_id):
        """获取用户平均分数"""
        with self.get_db_session() as session:
            result = session.query(Evaluation).join(InterviewSession).filter(
                InterviewSession.user_id == user_id
            ).all()
            
            if not result:
                return None
                
            total_score = sum(eval.overall_score for eval in result if eval.overall_score)
            return total_score / len(result) if result else 0

# 创建全局数据库管理器实例
db_manager = DatabaseManager()

# 初始化数据库
def init_database():
    """初始化数据库"""
    try:
        db_manager.create_tables()
        logger.info("数据库初始化完成")
        return True
    except Exception as e:
        logger.error(f"数据库初始化失败: {e}")
        return False

# 测试数据库连接
def test_database_connection():
    """测试数据库连接"""
    try:
        with db_manager.get_db_session() as session:
            # 简单查询测试连接
            from sqlalchemy import text
            result = session.execute(text("SELECT 1")).fetchone()
            logger.info("数据库连接测试成功")
            return True
    except Exception as e:
        logger.error(f"数据库连接测试失败: {e}")
        return False

if __name__ == "__main__":
    # 测试数据库功能
    print("测试数据库连接...")
    if test_database_connection():
        print("✅ 数据库连接正常")
        
        print("初始化数据库表...")
        if init_database():
            print("✅ 数据库表创建成功")
        else:
            print("❌ 数据库表创建失败")
    else:
        print("❌ 数据库连接失败")

